Creating running totals using a formula

If you have suppressed data, or your data is based on a formula that occurs WhilePrintingRecords, you should create a running total formula rather than using the Running Total Select Expert.

When you create a running total manually, you need to create three formulas:

In the following procedure, you will create a report that performs the following functions:

To create running totals using a formula
  1. Create a report using the sample data, Xtreme.mdb. Link the Customers and Orders tables and place the following fields from left to right in the Details section:

    {customer.CUSTOMER NAME}

    {orders.ORDER ID}

    {orders.ORDER AMOUNT}

  2. On the Insert menu, click Field Object.
  3. Select Formula Fields in the Field Explorer dialog box and click New.
  4. Name the formula "RunningTotal" and click OK.
  5. Enter the following into the Formula Editor:

    WhilePrintingRecords;

    CurrencyVar Amount;

    Amount := Amount + {orders.ORDER AMOUNT};

  6. Click Save and Close.
  7. Place this formula in the Details section of your report, just to the right of the {orders.ORDER AMOUNT} field.

    This formula prints the running total of the values in the Order Amount field.

  8. On the Insert menu, click Group and group the report on the {customer.CUSTOMER NAME} field.
  9. In the Formula Editor, create "AmountReset":

    WhilePrintingRecords;

    CurrencyVar Amount := 0;

    This formula says:

    Set the value in the Amount variable to 0.

  10. Place this formula in the Group Header #1 section of your report.

    Because the Group Header #1 section appears once for every group, @AmountReset will execute each time the group changes. Thus, the Amount variable is reset to 0 each time a new group begins.

  11. Select the @AmountReset formula on the report and use the Format Editor to suppress it so that it will not appear in the final print-out.
  12. In the Formula Editor, create "AmountDisplay":

    WhilePrintingRecords;

    CurrencyVar Amount;

    This formula simply displays the current value of the Amount variable at any time.

  13. Place this formula in the Group Footer #1 section of your report.

    Because the Group Footer #1 section appears once for every group, @AmountDisplay will execute each time a group ends. Thus, the value stored in the Amount variable will be printed each time the group changes.

    Note:    This formula prints the same value that @RunningTotal prints as the running total for the last record in each group. But since it is printing it in the Group Footer section, it acts as a group subtotal, not as a running total.



Seagate Software IMG Holdings, Inc.
http://www.seagatesoftware.com
Support services:
http://support.seagatesoftware.com